
package com.craterhill.java.ems.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.PreparedStatementCreator;

import com.craterhill.java.ems.common.beans.School;
import com.craterhill.java.ems.dao.impl.AbstractDao;
import com.craterhill.java.ems.dao.utils.EMSResultSetExtractor;

public interface SchoolDao extends EMSDao<School> {

	public final String FIELD_LIST = "obj.school_id, obj.school_name, obj.short_name, obj.school_type_id, obj.school_level_id, obj.title, obj.address, obj.town, obj.district, obj.country_id, obj.state_id, "
			+ "obj.zip_code, obj.contact, obj.email, obj.date_format_id, obj.logo_image_path, obj.banner_image_path, obj.school_description"
			+ AbstractDao.MONITER_FIELDS;

	public final String FIND_ALL = "select " + FIELD_LIST + " from schools obj";
	public final String FIND_BY_ID = "select " + FIELD_LIST + " from schools obj where obj.school_id = ?";

	public final String SAVE_SCHOOL = "insert into schools (school_name, short_name, school_type_id, school_level_id, title, address, town, district, country_id, state_id, zip_code, contact, email, date_format_id, logo_image_path, banner_image_path, school_description, created_by) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	public final String UPDATE_SCHOOL = "update schools set school_name = ?, short_name = ?, school_type_id = ?, school_level_id = ?, title = ?, address = ? , town = ?, district = ?, country_id = ?, state_id = ?, zip_code= ?, contact = ?, email = ?, date_format_id = ?, logo_image_path = ?, banner_image_path = ?, school_description = ?, modified_date = ? where school_id = ?";
	public final String DELETE_SCHOOL = "delete from schools where school_id = ?";

	public final class SchoolExtractor extends EMSResultSetExtractor<School> {

		@Override
		protected School extractResultSetData(ResultSet resultSet) throws SQLException {
			School school = new School();
			school.setSchoolId(resultSet.getInt(1));
			school.setSchoolName(resultSet.getString(2));
			school.setShortName(resultSet.getString(3));
			school.setSchoolTypeId(resultSet.getInt(4));
			school.setSchoolLevelId(resultSet.getInt(5));
			school.setTitle(resultSet.getString(6));
			school.setAddress(resultSet.getString(7));
			school.setTown(resultSet.getString(8));
			school.setDistrict(resultSet.getString(9));
			school.setCountryId(resultSet.getInt(10));
			school.setStateId(resultSet.getInt(11));
			school.setZipCode(resultSet.getInt(12));
			school.setContact(resultSet.getString(13));
			school.setEmail(resultSet.getString(14));
			school.setDateFormatId(resultSet.getInt(15));
			school.setLogoImagePath(resultSet.getString(16));
			school.setBannerImagePath(resultSet.getString(17));
			school.setSchoolDescription(resultSet.getString(18));
			school.setStatus(resultSet.getBoolean(19));
			school.setCreatedBy(resultSet.getInt(20));
			school.setCreatedDate(resultSet.getDate(21));
			school.setModifiedDate(resultSet.getDate(22));
			return school;
		}
	}

	public final class SchoolPreparedStatementCreator implements PreparedStatementCreator {

		private final int ACTION;
		private final School school;

		public SchoolPreparedStatementCreator(final int ACTION, final School school) {
			this.ACTION = ACTION;
			this.school = school;
		}

		@Override
		public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
			PreparedStatement preparedStatement = connection.prepareStatement(this.ACTION == EMSDao.ACTION_SAVE ? SAVE_SCHOOL : UPDATE_SCHOOL);

			preparedStatement.setString(1, school.getSchoolName());
			preparedStatement.setString(2, school.getShortName());
			preparedStatement.setInt(3, school.getSchoolTypeId());
			preparedStatement.setInt(4, school.getSchoolLevelId());
			preparedStatement.setString(5, school.getTitle());
			preparedStatement.setString(6, school.getAddress());
			preparedStatement.setString(7, school.getTown());
			preparedStatement.setString(8, school.getDistrict());
			preparedStatement.setInt(9, school.getCountryId());
			preparedStatement.setInt(10, school.getStateId());
			preparedStatement.setInt(11, school.getZipCode());
			preparedStatement.setString(12, school.getContact());
			preparedStatement.setString(13, school.getEmail());
			preparedStatement.setInt(14, school.getDateFormatId());
			preparedStatement.setString(15, school.getLogoImagePath());
			preparedStatement.setString(16, school.getBannerImagePath());
			preparedStatement.setString(17, school.getSchoolDescription());

			if(this.ACTION == EMSDao.ACTION_SAVE){
				preparedStatement.setInt(18, school.getCreatedBy());
			} else{
				preparedStatement.setDate(18, school.getModifiedDate());
				preparedStatement.setInt(19, school.getSchoolId());
			}

			return preparedStatement;
		}
	}
}
